﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Web;
using System.Configuration;

namespace Hutech.Authentication.Library.Data
{
    /// <summary>
    /// Lop mo ta cac ket noi voi CSDL cua doi tuong SiteUser.
    /// Author:         Le Xuan Manh
    /// Created Date:   08/3/2010
    /// </summary>
    internal static class DBSiteUser
    {
        private static string GetConnectionString()
        {
            return ConfigurationManager.AppSettings["MSSQLConnectionString_CustommerService27032013"];
        }

        internal static bool Create(
        Guid _UserId,
        Guid _SiteId,
        string _LoginName,
        string _Email,
        string _Name,
        string _Password,
        DateTime _DateCreated,
        string _PasswordSalt,
        string _Comment,
        bool _IsBuildInUser
        )
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_Insert", 10);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _UserId);
            sph.DefineSqlParameter("@SiteId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _SiteId);
            sph.DefineSqlParameter("@LoginName", SqlDbType.NVarChar, ParameterDirection.Input, _LoginName);
            sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, ParameterDirection.Input, _Email);
            sph.DefineSqlParameter("@Name", SqlDbType.NVarChar, ParameterDirection.Input, _Name);
            sph.DefineSqlParameter("@Password", SqlDbType.NVarChar, ParameterDirection.Input, _Password);
            sph.DefineSqlParameter("@DateCreated", SqlDbType.DateTime, ParameterDirection.Input, _DateCreated);
            sph.DefineSqlParameter("@PasswordSalt", SqlDbType.NVarChar, ParameterDirection.Input, _PasswordSalt);
            sph.DefineSqlParameter("@Comment", SqlDbType.NVarChar, ParameterDirection.Input, _Comment);
            sph.DefineSqlParameter("@IsBuildInUser", SqlDbType.Bit, ParameterDirection.Input, _IsBuildInUser);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool Update(
        Guid _UserId,
        Guid _SiteId,
        string _LoginName,
        string _Email,
        string _Name,
        string _Password,
        string _PasswordSalt,
        string _Comment)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_Update", 8);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _UserId);
            sph.DefineSqlParameter("@SiteId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _SiteId);
            sph.DefineSqlParameter("@LoginName", SqlDbType.NVarChar, ParameterDirection.Input, _LoginName);
            sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, ParameterDirection.Input, _Email);
            sph.DefineSqlParameter("@Name", SqlDbType.NVarChar, ParameterDirection.Input, _Name);
            sph.DefineSqlParameter("@Password", SqlDbType.NVarChar, ParameterDirection.Input, _Password);
            sph.DefineSqlParameter("@PasswordSalt", SqlDbType.NVarChar, ParameterDirection.Input, _PasswordSalt);
            sph.DefineSqlParameter("@Comment", SqlDbType.NVarChar, ParameterDirection.Input, _Comment);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool UpdatePassword(
        Guid _UserId,
        string _Password,
        string _PasswordSalt)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_UpdatePassword", 3);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _UserId);
            sph.DefineSqlParameter("@Password", SqlDbType.NVarChar, ParameterDirection.Input, _Password);
            sph.DefineSqlParameter("@PasswordSalt", SqlDbType.NVarChar, ParameterDirection.Input, _PasswordSalt);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool Delete(Guid _UserId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_Delete_2", 1);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _UserId);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);
        }

        internal static bool ForceDelete(Guid _UserId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_Delete", 1);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _UserId);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);
            ;
        }

        /// <summary>
        /// Get all
        /// </summary>
        /// <returns></returns>
        internal static IDataReader GetUsers()
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_SelectAll", 0);
            return sph.ExecuteReader();
        }

        // Lay theo PrimaryKey
        internal static IDataReader GetUser(Guid _UserId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_SelectOne", 1);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _UserId);
            return sph.ExecuteReader();
        }

        // [User_SelectByLoginName]
        internal static IDataReader GetSingleUserByLoginName(string _LoginName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_SelectByLoginName", 1);
            sph.DefineSqlParameter("@LoginName", SqlDbType.NVarChar, ParameterDirection.Input, _LoginName);
            return sph.ExecuteReader();
        }

        internal static IDataReader GetBySite(Guid _SiteId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_SelectBy_SiteId", 1);
            sph.DefineSqlParameter("@SiteId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _SiteId);
            return sph.ExecuteReader();
        }
        internal static string Login(string loginName, string password)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_Login", 3);
            sph.DefineSqlParameter("@LoginName", SqlDbType.NVarChar, 50, ParameterDirection.Input, loginName);
            sph.DefineSqlParameter("@Password", SqlDbType.NVarChar, 1000, ParameterDirection.Input, password);
            sph.DefineSqlParameter("@UserName", SqlDbType.NVarChar, 100, ParameterDirection.InputOutput, null);
            sph.ExecuteNonQuery();


            if (sph.Parameters[2] != null)
            {
                return sph.Parameters[2].Value.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        internal static IDataReader GetRolesByUser(Guid userId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_GetUserRoles", 1);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userId);
            return sph.ExecuteReader();
        }

        //[get_manhanvien]
        internal static IDataReader GetLoginNameByUserID(Guid userID)
        {
            string sql = @"select LoginName from root_Users where userid = N'" + userID + "'";
            return SqlHelper.ExecuteReader(GetConnectionString(), CommandType.Text, sql, null);
        }



        //tsandtm them

        /// <summary>
        /// Gets a count of rows in the root_Users table.
        /// </summary>
        public static int GetCount()
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(
            GetConnectionString(),
            CommandType.StoredProcedure,
            "root_Users_GetCount",
            null));
        }
        internal static IDataReader GetAllUser(int pageNumber, int pageSize, out int totalPages)
        {
            totalPages = 1;
            int totalRows = GetCount();

            if (pageSize > 0)
                totalPages = totalRows / pageSize;

            if (totalRows <= pageSize)
            {
                totalPages = 1;
            }
            else
            {
                int remainder;
                Math.DivRem(totalRows, pageSize, out remainder);
                if (remainder > 0)
                {
                    totalPages += 1;
                }
            }

            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_SelectPageAllUser", 2);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return sph.ExecuteReader();
        }

        public static IDataReader GetSingleUser(string email)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Users_SelectByEmail", 1);
            sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 100, ParameterDirection.Input, email);
            return sph.ExecuteReader();
        }
    }
}
